import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 
 */

/**
 * @author ysitu
 *
 */
public class InsertDerby {
	private static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private static String database = "jdbc:derby:derbyDB";
	
	public static void main(String[] args) {
		try {
			Class.forName(driver);
			Properties prop = new Properties();
			prop.put("user", "user1");
			prop.put("password", "password1");
			Connection conn = DriverManager.getConnection(database,prop);
			conn.setAutoCommit(false);
			Statement stat = conn.createStatement();
			stat.execute("alter table location alter column num not null");
			stat.execute("alter table location add primary key(num)");
			stat.execute("create table client(client_id int, client_name varchar(80), location_id int,primary key(client_id), foreign key(location_id) references location(num))");
			
			PreparedStatement ps = conn.prepareStatement("insert into client values(?,?,?)");
			ps.setInt(1, 1);
			ps.setString(2, "Jack Jones");
			ps.setInt(3, 300);
			ps.executeUpdate();
			
			ps.setInt(1, 2);
			ps.setString(2, "Ben Gates");
			ps.setInt(3, 1910);
			ps.executeUpdate();
			
			ps.setInt(1, 3);
			ps.setString(2, "David Corpofield");
			ps.setInt(3, 300);
			ps.executeUpdate();
			
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}
